package com.tim.helper.analysis;

import cn.hutool.core.collection.ListUtil;
import com.alibaba.excel.EasyExcel;
import com.tim.MainHelper;
import com.tim.third.demo.easyexcel.DemoData;
import com.tim.third.demo.easyexcel.DemoDataListener;
import com.tim.util.DbUtils;
import org.apache.commons.collections.CollectionUtils;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.LongAdder;

/**
 * 1，Zero date value prohibited。
 *  https://blog.csdn.net/yongbutingxide/article/details/89706635
 *  带参数：?zeroDateTimeBehavior=CONVERT_TO_NULL
 * 2，数据备份
 *  ddl、数据备份跑本类
 *  数据表ddl备份，可走idea database
 */
public class BackupDbHelper {
    // 懒猪 猪耳朵 每日说 贝小集 懒猪同城 懒猪探店
//    String project = "每日说";
    String project = "贝小集";
    int num = 1000;
    int sleepSec = 1;
    // 数据库信息；before方法中按project设置
    String username,password,dbname, ipPort;

    // 以下一般不改
    List<String> dbList;
    String excludes = "information_schema,mysql,test,sys,__recycle_bin__,performance_schema," +
            "test2,xqxc_report";
    LongAdder totalTable = new LongAdder();
    LongAdder totalRecTable = new LongAdder();

    @Test
    public void fast(){
        File dir = new File(MainHelper.OUTPUT_DIR);
        if(!dir.exists()){
            dir.mkdirs();
        }
        Arrays.stream(dir.listFiles())
                .forEach(f->System.out.println(f.getAbsolutePath()));
    }
    @Test
    public void backupDdl() throws Exception {
        Map<String, List<String>> schemaTablesMap = getSchemaTablesMap();
        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+dbname+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        File dir = new File(MainHelper.OUTPUT_DIR);
        if(!dir.exists()){
            dir.mkdirs();
        }
        Arrays.stream(dir.listFiles((d, name)->name.toLowerCase().trim().endsWith("ddl.sql")))
                .forEach(File::deleteOnExit);

        schemaTablesMap
                .keySet()
                .forEach(
                        db -> {
                            try {
                                String query = String.format("show create database %s;", db);
                                Statement stmt = connection.createStatement();
                                ResultSet resultSet = stmt.executeQuery(query);
                                if (resultSet.next()) {
                                    String createDb = resultSet.getString(2);
                                    Files.write(Paths.get(MainHelper.OUTPUT_DIR, "DDL.sql"),
                                            Arrays.asList(createDb, "\n"),
                                            StandardOpenOption.CREATE,
                                            StandardOpenOption.APPEND);
//                                    System.out.println(createDb);
                                }
                                resultSet.close();
                                for(String table : schemaTablesMap.get(db)){
                                    query = String.format("show create table `%s`.`%s`;", db, table);
                                    resultSet = stmt.executeQuery(query);
                                    if (resultSet.next()) {
                                        String createTable = resultSet.getString(2);
                                        Files.write(Paths.get(MainHelper.OUTPUT_DIR, db+"DDL.sql"),
                                                Arrays.asList(createTable, "\n"),
                                                StandardOpenOption.CREATE,
                                                StandardOpenOption.APPEND);
//                                        System.out.println(createTable);
                                    }
                                    resultSet.close();

                                }
                                stmt.close();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        });
        connection.close();
    }
    @Test
    public void backupData() throws Exception {
        Map<String, List<String>> schemaTablesMap = getSchemaTablesMap();

        ExecutorService executorService = Executors.newFixedThreadPool(Math.min(10, schemaTablesMap.size()));
        schemaTablesMap.forEach((k,v)->{
            String destDir = MainHelper.OUTPUT_DIR+"/"+v+"/";
            File dir = new File(destDir);
            if(!dir.exists()){
                dir.mkdirs();
            }
            ListUtil.split(v, 20).forEach(e-> {
                executorService.submit(()-> {
                    try {
                        handleDb(k,e);
                    } catch (Exception exception) {
                        exception.printStackTrace();
                    }
                });
            });
        });
        executorService.shutdown();
        executorService.awaitTermination(10, TimeUnit.MINUTES);
    System.out.println("SUCCESS, totalTable="+totalTable.sum());
    System.out.println("SUCCESS, totalRecTable="+totalRecTable.sum());
    }

    @Test
    public void explainSql() throws Exception {
        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+dbname+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        String file = "";

        List<LinkedHashMap<Integer, String>> list = EasyExcel.read(file)
                .sheet()// 切换sheet
                .headRowNumber(1)// 表头行数
                .doReadSync();
        Statement statement = connection.createStatement();
        for (int i = 0; i < list.size(); i++) {
            // 对应excel行，i+2
            String sql = list.get(i).get(0)
                    .replaceAll("limit\\s+\\?", "limit 1")
                    .replaceAll("limit\\s+\\d+\\s*,\\s*\\?", "limit 5,1")
                    .replaceAll("\\?", "'1'")
                    .replaceAll("[\r\n]+", " ")
                    .trim();
            System.out.println(sql);
            String upperCase = sql.toUpperCase();
            if(upperCase.startsWith("UPDATE")||upperCase.startsWith("DELETE")||upperCase.startsWith("INSERT")){
                continue;
            }
            statement.execute("explain " + sql);
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            String type = DbUtils.getColumnValue(Types.VARCHAR, 4, resultSet);
            String possibleKey = DbUtils.getColumnValue(Types.VARCHAR, 5, resultSet);
            System.out.println(i+",\t"+type+",\t"+possibleKey);
            if("'ALL'".equals(type)){
                System.err.println(sql);
            }
        }
    }

    private Map<String, List<String>> getSchemaTablesMap() throws Exception {
        if(CollectionUtils.isEmpty(dbList)){
            throw new RuntimeException();
        }

        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+dbname+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        Statement stmt = connection.createStatement();
        String queryTable = "SELECT * FROM information_schema.TABLES t where t.TABLE_SCHEMA not in ('"
                +excludes.replaceAll(",", "','")+"') and engine is not null;";
        System.out.println(queryTable);
        ResultSet tableResultSet = stmt.executeQuery(queryTable);
        Map<String, List<String>> schemaTablesMap = new HashMap<>();
        while (tableResultSet.next()){
            String tableSchema = tableResultSet.getString("table_schema");
            String tableName = tableResultSet.getString("table_name");
            if(!schemaTablesMap.containsKey(tableSchema)){
                schemaTablesMap.put(tableSchema, new ArrayList<>());
            }
            schemaTablesMap.get(tableSchema).add(tableName);
        }
        tableResultSet.close();
        stmt.close();
        connection.close();
        return schemaTablesMap;
    }

    private void handleDb(String db, List<String> tables) throws Exception {
        int count=0;
        Connection connection = DbUtils.getConnection(
                "jdbc:mysql://"+ ipPort+db+"?zeroDateTimeBehavior=CONVERT_TO_NULL",
                username, password);
        String destDir = MainHelper.OUTPUT_DIR+"/"+db+"/";

        for(String table : tables){
            String check = String.format("select * from `%s`.`%s` limit 1;", db, table);
            System.out.println(count++ + "\t"+check);
            totalTable.increment();
            Statement stmt = connection.createStatement();
            ResultSet checkResultSet = stmt.executeQuery(check);
            String orderBy = "";
            if(checkResultSet.next()){
                String firstCol = checkResultSet.getMetaData().getColumnName(1);
                orderBy = "order by `"+firstCol+"` desc";
            }else{
                continue;
            }
            totalRecTable.increment();
            String query = String.format("select * from `%s`.`%s` %s limit %s;", db, table, orderBy, num);
//                System.out.println(query);
            ResultSet resultSet = stmt.executeQuery(query);
            String insertSql = DbUtils.rsToInsert(resultSet);
            checkResultSet.close();
            resultSet.close();
            stmt.close();
//        System.out.println("\twrite "+table);
            Files.write(Paths.get(destDir+table+".sql"),
                    Arrays.asList(insertSql),
                    StandardOpenOption.CREATE);
//                System.out.println("\twrite end "+table);
//                Thread.sleep(sleepSec*1000);
//		  System.out.println(insertSql);
        }
        connection.close();
    }

    @Before
    public void before() throws Exception {
        // 懒猪 猪耳朵 每日说 贝小集 懒猪同城 懒猪探店
        if("每日说".equals(project)){
            userDailyDb();
        }else if("贝小集".equals(project)){
            userBxjDb();
        }else{
            throw new RuntimeException("project 参数不正确");
        }

    }

    private void userDailyDb(){
        username = "dev_root";
        password = "Dev@20220621!";
        dbList = Arrays.asList(("xqxc_admin,xqxc_goods,xqxc_rider," +
                "xqxc_advert,xqxc_hotel,xqxc_settle,xqxc_center_order," +
                "xqxc_marketing,xqxc_show,xqxc_charge,xqxc_merchant," +
                "xqxc_user,xqxc_dance,xqxc_order,xqxc_user2," +
                "xqxc_earnings,xqxc_pay,xqxc_xiudou,xqxc_flink," +
                "xqxc_quartz").split(","));
        dbname = "xqxc_admin";
        ipPort = "rm-bp1qvq6wr6hn6gidlpm.mysql.rds.aliyuncs.com:3306/";

    }

    private void userBxjDb(){
        username = "dev_admin";
        password = "Dev+2019@xqxc";
        dbList = Arrays.asList(("xqxc_admin,xqxc_advert," +
                "xqxc_collector,xqxc_dance,xqxc_earnings," +
                "xqxc_flink,xqxc_goods,xqxc_hotel," +
                "xqxc_marketing,xqxc_merchant,xqxc_order," +
                "xqxc_pay,xqxc_quartz,xqxc_report," +
                "xqxc_rider,xqxc_settle,xqxc_show," +
                "xqxc_user,xqxc_xiudou").split(","));
        dbname = "xqxc_admin";
        ipPort = "rm-bp16tdzf48mvnql762o.mysql.rds.aliyuncs.com:3306/";

    }


}
